package com.servlet;

import java.io.IOException;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.text.SimpleDateFormat;
import javax.naming.Context;
import javax.naming.InitialContext;
import javax.servlet.RequestDispatcher;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.sql.DataSource;

/**
 * Servlet implementation class SearchFlightServletByBooking
 */
public class SearchFlightServletByBooking extends HttpServlet {
	private static final long serialVersionUID = 1L;
	
private final static String DATASOURCE_NAME = "jdbc/FlyGreenwichAirline";
	
	private DataSource datasource;
       
    /**
     * @see HttpServlet#HttpServlet()
     */
    public SearchFlightServletByBooking() {
        super();
        // TODO Auto-generated constructor stub
    }

	/**
	 * @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
	 */
	protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		doPost(request, response);
	}

	/**
	 * @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
	 */
	protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		
		String name = "";
		String depart = "";
		String dest = "";
		String cost = "";
		int seat_no = 0;
		Timestamp selecttime;
		
		String time = "";
		
		java.sql.Connection con=null;
		
		String bookingno = request.getParameter("booking_no");
		Integer bookno = Integer.parseInt(bookingno);
		
		System.out.println("bookingno: " + bookingno);

    	String query = "SELECT name, depart, dest, cost, seat_no, select_time " +
    					"FROM customers, flight, transactions "+
    					"WHERE customers.id = transactions.customer_id "+
    					"AND transactions.flight_id = flight.flight_no "+
    					"AND booking_no = "+ bookno +";";

						
    	try {
    		Context env = (Context) new InitialContext().lookup("java:comp/env");
			datasource = (DataSource) env.lookup(DATASOURCE_NAME);
      		
      		con = datasource.getConnection();

            java.sql.Statement stmt = con.createStatement();
            ResultSet rs = stmt.executeQuery (query);
            
            while(rs.next()){
            name= rs.getString(1);
            depart = rs.getString(2);
            dest = rs.getString(3);
            cost = rs.getString(4);
            seat_no = rs.getInt(5);
            selecttime = rs.getTimestamp(6);
            time = new SimpleDateFormat("MM/dd/yyyy").format(selecttime); 
            
            System.out.println("name: "+name);
            System.out.println("depart: "+depart);
            System.out.println("dest: "+dest);
            System.out.println("cost: "+cost);
            System.out.println("seat_no: "+seat_no);
            System.out.println("selecttime: "+time);

            }
               	
            rs.close();
            stmt.close();
            
        }  // end try

    catch (SQLException ex) {
            
		while (ex != null) {  
				ex.printStackTrace();
              }  // end while

        }  // end catch SQLException

   catch (java.lang.Exception ex) {
      	ex.printStackTrace();
	  }
    	
    	finally{
    		try{
    			if (con != null) con.close();
    		}catch(SQLException ignored){
    		ignored.printStackTrace();
    		}
    	}
    	
    	
    	request.setAttribute("name", name);
        request.setAttribute("depart", depart);
        request.setAttribute("dest", dest);
        request.setAttribute("cost", cost);
        request.setAttribute("seat_no", seat_no);
        request.setAttribute("selecttime", time);
        request.setAttribute("bookingno", bookingno);

    	String nextJSP = "/searchBookingResults.jsp";
    	RequestDispatcher dispatcher = getServletContext().getRequestDispatcher(nextJSP);
    	dispatcher.forward(request,response);
	}

}
